import datetime
import json
import pandas as pd

import pymysql

"""查询视频播放总时长"""


# 1上市生产 2上市测试 3非上市生产 4非上市测试
def get_connection(type):
    host = 'rm-2zej74580n3kmf93w9o.mysql.rds.aliyuncs.com'
    user = 'cloudpc_prod'
    password = 'k_z8_tKhS7WF_RCm'
    database = 'cloudpc_prod'
    charset = 'utf8'
    if type == '2':
        host = 'rm-2ze86ia90s73c90pw0o.mysql.rds.aliyuncs.com'
        user = 'cloudpc_dev'
        password = 'w0RZMCO_La3o'
        database = 'cloudpc_dev'
        charset = 'utf8'
    elif type == '3':
        host = 'rm-2ze98l8dhl893u752ao.mysql.rds.aliyuncs.com'
        user = 'cloudpc_prod'
        password = '36nEY($Z(tTn9gK'
        database = 'cloudpc_prod'
        charset = 'utf8'
    elif type == '4':
        host = 'rm-2ze98l8dhl893u752ao.mysql.rds.aliyuncs.com'
        user = 'cloudpc_dev'
        password = 'O4p&Iw&%B1nXYv'
        database = 'cloudpc_dev'
        charset = 'utf8'
    print('当前数据库是%s%s' % (get_type_name(type), user))
    con = pymysql.connect(host=host, user=user, password=password, db=database,
                          charset=charset)
    return con


def select_by_sql(con, sql):
    cursor = con.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    col_name = cursor.description
    rows = []
    for row in result:
        d = {}
        for col in range(len(col_name)):
            key = col_name[col][0]
            value = row[col]
            if isinstance(value, datetime.datetime):
                value = value.strftime('%Y-%m-%d %H:%M:%S')
            d[key] = str(value)
        rows.append(d)
    js = json.dumps(rows, sort_keys=True, ensure_ascii=False, separators=(',', ':'))
    return js


# 1上市生产 2上市测试 3非上市生产 4非上市测试
def get_type_name(type):
    type_name = ''
    if type == '1':
        type_name = '1上市生产'
    elif type == '2':
        type_name = '2上市测试'
    elif type == '3':
        type_name = '3非上市生产'
    elif type == '4':
        type_name = '4非上市测试'
    return type_name


def find_video_duration(type, begin_time, end_time):
    type_name = get_type_name(type)
    duration = 0
    # 获取数据库连接
    con = get_connection(type)

    # 查询已经关闭的观看时长
    sql = "select \
			'{}' as type, \
                    DATE_FORMAT(a.start_time, '%Y-%m-%d') as date,\
               com_name as com_name,\
                     org_code as org_code,\
               org_name as org_name,\
                     a.farm_code farm_code,\
               farm_name as farm_name,\
                     a.user_name,\
                     a.iot_device_id,\
               sum(TIMESTAMPDIFF(second, start_time, end_time)) as duration \
        from iot_camera_use_record a\
        where\
        start_time is not null and end_time is not null\
        and a.start_time between '{}' and '{}'\
        group by a.user_name,a.device_id,DATE_FORMAT(a.start_time, '%Y-%m-%d')\
        order by DATE_FORMAT(a.start_time, '%Y-%m-%d'),a.org_code,a.farm_code,a.user_name".format(
        type_name, begin_time, end_time);
    con = get_connection(type)
    rs = select_by_sql(con, sql)

    con.close()
    return rs


if __name__ == '__main__':
    duration = 0
    begin_time = '2024-05-06 00:00:00'
    end_time = '2024-05-12 23:59:59'
    data = []
    for i in (1, 2, 3, 4):
        # 查询时间
        rs = find_video_duration(str(i), begin_time, end_time)
        list = json.loads(rs)
        data.extend(list)
    # 打印结果
    print(data)
    path = "C:/Users/yangjianzhang/Desktop/"
    df = pd.json_normalize(data)
    df.to_excel(path + 'test.xlsx', index=False)
